package com.cruiser.data.export;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 数据导出
 *
 * @author billymac
 * @version 1.0
 * @date 17/6/13  下午5:20
 * @since JDK1.8
 */
public class XlsxExport {

    /**
     * 获取IP列表
     * @param sourcePath
     * @param index
     * @return
     * @throws IOException
     */
    public Set<String> getColumns(String sourcePath, int index) throws IOException {
        Set<String> result = new TreeSet<String>();
        InputStream source = new FileInputStream(sourcePath);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(source);
        XSSFSheet XSSFSheet = xssfWorkbook.getSheetAt(0);
        if (XSSFSheet != null) {
            int lastNum = XSSFSheet.getLastRowNum();
            for (int i = 0; i < lastNum; i++) {
                XSSFRow XSSFSheetRow = XSSFSheet.getRow(i);
                XSSFCell cell = XSSFSheetRow.getCell(index);
                String ip = cell.getStringCellValue();
                if (ip.contains("/"))
                    result.addAll(Arrays.asList(ip.split("/")));
                else
                    result.add(ip);
            }

        }
        return result;
    }


    public void timestampToDateString(String sourcePath, int index) throws IOException {
        InputStream source = new FileInputStream(sourcePath);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(source);
        XSSFSheet XSSFSheet = xssfWorkbook.getSheetAt(0);
        if (XSSFSheet != null) {
            int lastNum = XSSFSheet.getLastRowNum();
            for (int i = 0; i <= lastNum; i++) {
                XSSFRow XSSFSheetRow = XSSFSheet.getRow(i);
                XSSFCell cell = XSSFSheetRow.getCell(index);
                long time = (long) cell.getNumericCellValue();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                cell.setCellValue(sdf.format(new Date(time * 1000l)));
//                String ip = cell.getStringCellValue();
//                if (ip.contains("/"))
//                    result.addAll(Arrays.asList(ip.split("/")));
//                else
//                    result.add(ip);
            }

        }
        OutputStream out = new FileOutputStream(sourcePath);
        xssfWorkbook.write(out);
        out.close();
    }

    /**
     *
     * @param sourcePath
     * @param index
     * @throws IOException
     */
    public void dealRemind(String sourcePath, int index) throws IOException {
        InputStream source = new FileInputStream(sourcePath);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(source);
        XSSFSheet XSSFSheet = xssfWorkbook.getSheetAt(0);
        if (XSSFSheet != null) {
            int lastNum = XSSFSheet.getLastRowNum();
            for (int i = 0; i <= lastNum; i++) {
                XSSFRow XSSFSheetRow = XSSFSheet.getRow(i);
                XSSFCell cell = XSSFSheetRow.getCell(index);
                try {
                    String remind = cell.getStringCellValue().trim();
                    if (remind != null && !"--".equals(remind.trim())) {
                        if (remind.contains("变更号") && (remind.contains("补录变更申请人") ||
                                remind.contains("申请人") || remind.contains("变更申请人"))) {
                            String first = "变更号";

                            String man = null;
                            if (remind.contains("补录变更申请人"))
                                man = "补录变更申请人";
                            else if (remind.contains("变更申请人"))
                                man = "变更申请人";
                            else if (remind.contains("申请人"))
                                man = "申请人";

                            String target = remind;
                            String changeNum = null;
                            String changeMan = null;
                            int ind1 = remind.lastIndexOf(first);
                            int ind2 = remind.lastIndexOf(man);
                            if (ind1 > ind2) {
                                String[] ss = remind.split(man);
                                target = ss[ss.length-1];
                                changeMan = target.split(first)[0];
                                changeNum = target.split(first)[1];
                            } else {
                                String[] ss = remind.split(first);
                                target = ss[ss.length-1];
                                changeMan = target.split(man)[1];
                                changeNum = target.split(man)[0];
                            }
                            changeNum = changeNum.replaceAll(":","");
                            changeNum = changeNum.replaceAll("：","");
                            changeNum = changeNum.replaceAll(";","");
                            changeNum = changeNum.replaceAll("；","");
                            changeNum = changeNum.replaceAll(",","");

                            changeMan = changeMan.replaceAll(":","");
                            changeMan = changeMan.replaceAll("：","");
                            changeMan = changeMan.replaceAll(";","");
                            changeMan = changeMan.replaceAll("；","");
                            changeMan = changeMan.replaceAll(",","");
                            cell.setCellValue(changeNum + ":" +changeMan);

                        } else {
                            cell.setCellValue("");
                        }
                    } else {
                        cell.setCellValue("");
                    }
                } catch (Exception var) {
                    System.out.println(cell.getStringCellValue());
                    cell.setCellValue("");
                    var.printStackTrace();
                }
            }

        }
        OutputStream out = new FileOutputStream(sourcePath);
        xssfWorkbook.write(out);
        out.close();
    }


    public void dealLineFeed(String sourcePath) throws IOException {
        InputStream source = new FileInputStream(sourcePath);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(source);
        XSSFSheet XSSFSheet = xssfWorkbook.getSheetAt(0);
        if (XSSFSheet != null) {
            int lastNum = XSSFSheet.getLastRowNum();
            for (int i = 0; i <= lastNum; i++) {
                XSSFRow XSSFSheetRow = XSSFSheet.getRow(i);
                int lastCellNum = XSSFSheetRow.getLastCellNum();
                for (int j = 0; j < lastCellNum; j++) {
                    XSSFCell cell = XSSFSheetRow.getCell(j);
                    if (cell ==null)
                        continue;

                    String content = null;
                    try {
                        content = cell.getStringCellValue();
                    } catch (Exception var) {
                        System.out.println("row : " + i + ", columns : " + j);
                        continue;
                    }
                    if (content != null && content.contains("\n")) {
                        System.out.println(content);
                        content = content.replaceAll("\n", "");
                        cell.setCellValue(content);
                        System.out.println(content);
                    }
                }
            }

        }
        OutputStream out = new FileOutputStream(sourcePath);
        xssfWorkbook.write(out);
        out.close();
    }

    public void readXls(String sourcePath, String targetPath) throws IOException {
        InputStream source = new FileInputStream(sourcePath);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(source);
        XSSFSheet XSSFSheet = xssfWorkbook.getSheetAt(0);
        Map<String, String> result = new TreeMap<String, String>();
        if (XSSFSheet == null) {
            return;
        }

        XSSFRow first = XSSFSheet.getRow(0);
        int ipIndex = 0;
        int businessIndex = 0;
        for (int cellNum = 0; cellNum <= first.getLastCellNum(); cellNum++) {
            XSSFCell XSSFCell = first.getCell(cellNum);
            if (XSSFCell == null) {
                continue;
            }
            if("CIMS_IP".equals(getValue(XSSFCell))) {
                ipIndex = cellNum;
            }

            if("IP比对结果".equals(getValue(XSSFCell))) {
                businessIndex = cellNum;
            }
        }

        // 循环行Row
        for (int rowNum = 1; rowNum <= XSSFSheet.getLastRowNum(); rowNum++) {
            XSSFRow XSSFRow = XSSFSheet.getRow(rowNum);
            if (XSSFRow == null) {
                continue;
            }

            XSSFCell ipCell = XSSFRow.getCell(ipIndex);
            String ip = getValue( ipCell);
            XSSFCell businessCell = XSSFRow.getCell(businessIndex);
            String businessName = getValue( businessCell);
            result.put(ip, businessName);
        }
        //更新excel表格
        updateTargetXlsx(targetPath, result);
    }

    private void updateTargetXlsx(String targetPath, Map<String, String> ipMaps) throws IOException {
        InputStream source = new FileInputStream(targetPath);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(source);
        XSSFSheet XSSFSheet = xssfWorkbook.getSheetAt(0);
        if (XSSFSheet == null) {
            return;
        }

        XSSFRow first = XSSFSheet.getRow(0);
        int ipIndex = 0;
        int businessIndex = 0;
        for (int cellNum = 0; cellNum <= first.getLastCellNum(); cellNum++) {
            XSSFCell XSSFCell = first.getCell(cellNum);
            if (XSSFCell == null) {
                continue;
            }
            if("CIMS_IP".equals(getValue(XSSFCell))) {
                ipIndex = cellNum;
            }

            if("IP比对结果".equals(getValue(XSSFCell))) {
                businessIndex = cellNum;
            }
        }

        // 循环行Row
        for (int rowNum = 1; rowNum <= XSSFSheet.getLastRowNum(); rowNum++) {
            XSSFRow XSSFRow = XSSFSheet.getRow(rowNum);
            if (XSSFRow == null) {
                continue;
            }

            XSSFCell ipCell = XSSFRow.getCell(ipIndex);
            String ip = getValue(ipCell);
            XSSFCell businessCell = XSSFRow.getCell(businessIndex);
            businessCell.setCellValue(ipMaps.get(ip));
        }
        OutputStream out = new FileOutputStream(targetPath);
        xssfWorkbook.write(out);
        out.close();
    }

    private String getValue(XSSFCell hssfCell) {
        try {
            return String.valueOf(hssfCell.getStringCellValue());
        } catch(Exception var) {
            return "ERROR";
        }
    }
}
